using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace MDSAdministration
{
    public delegate void ChangedEventHandler(object sender, string message);

    public class EntityData
    {
        public ServerMDSDetails ServerA { get; set; }
        public ServerMDSDetails ServerB { get; set; }
        public CheckStates cStates { get; set; }
        private Dictionary<string, string> dTableAlias = new Dictionary<string, string>();
        string selectAttributes = "";
        string joins = "";
        string hashAttribute = "";

        public Dictionary<String, EntityColumn> EntityColumns;

        public event ChangedEventHandler Changed;
        
        // Invoke the Changed event;
        protected void OnProgressUpdate(string message)
        {
            if (Changed != null)
                Changed(this, message);
        }

        public Dictionary<string, EntityDataCompreDetails> GetEntityList(ServerDirection sDirection)
        {
            DataSet dsEntitiesA = DataHelper.GetData(BuildEntityListQuery(ServerA), ServerA);
            DataSet dsEntitiesB = DataHelper.GetData(BuildEntityListQuery(ServerB), ServerB);

            Dictionary<string, EntityDataCompreDetails> dEntities = new Dictionary<string, EntityDataCompreDetails>();

            foreach (DataRow r in dsEntitiesA.Tables[0].Rows)
            {
                OnProgressUpdate("Building Server A Entity: " + r["Name"].ToString());
                if (!dEntities.ContainsKey(r["Name"].ToString()))
                {
                    dEntities.Add(r["Name"].ToString(), new EntityDataCompreDetails() { EntityExistOnServerA = true, Name = r["Name"].ToString(), EntityMUIDA = r["EntityMUID"].ToString(), StagingBaseA = ((ServerA.schemaVersion.VersionDetails.HasStagingTable) ? r["StagingBase"].ToString() : null) });
                }
            }

            foreach (DataRow r in dsEntitiesB.Tables[0].Rows)
            {
                OnProgressUpdate("Building Server B Entity: " + r["Name"].ToString());
                if (!dEntities.ContainsKey(r["Name"].ToString()))
                {
                    dEntities.Add(r["Name"].ToString(), new EntityDataCompreDetails() { EntityExistOnServerB = true, Name = r["Name"].ToString(), EntityMUIDB = r["EntityMUID"].ToString(), StagingBaseB = ((ServerB.schemaVersion.VersionDetails.HasStagingTable) ? r["StagingBase"].ToString() : null) });
                }
                else
                {
                    if(sDirection == ServerDirection.ServerB)
                    {
                        dEntities[r["Name"].ToString()].StagingBaseB = ((ServerB.schemaVersion.VersionDetails.HasStagingTable)
                                                                           ? r["StagingBase"].ToString()
                                                                           : null);
                    }
                    dEntities[r["Name"].ToString()].EntityExistOnServerB = true;
                    dEntities[r["Name"].ToString()].EntityMUIDB = r["EntityMUID"].ToString();
                }
            }

            return dEntities;
        }

        public string BuildEntityListQuery(ServerMDSDetails server)
        {
            return "SELECT e.[Name], e.[MUID] AS 'EntityMUID' "
                + ((server.schemaVersion.VersionDetails.HasStagingTable) ? ", e.[StagingBase] " : "")
                + "FROM mdm.tblModel m" + Environment.NewLine
                + "INNER JOIN mdm.tblEntity e ON m.[ID] = e.[Model_ID]" + Environment.NewLine
                + "WHERE m.[Name] = '" + server.Model + "'" + Environment.NewLine
                + "ORDER BY e.[Name]";
        }

        public DataSet GetEntityData(EntityDataCompreDetails ed)
        {
            if (BuildEntityDataQueries(ed) == false) return null;


            OnProgressUpdate("Getting Server A Data for Entity: " + entityNameA);
            DataSet dsEntityDataA = DataHelper.GetData(queryServerA, ServerA);

            OnProgressUpdate("Getting Server B Data for Entity: " + entityNameB);
            DataSet dsEntityDataB = DataHelper.GetData(queryServerB, ServerB);

            OnProgressUpdate("Merge Data for Entity: " + entityNameB);
            return mergeDataSets(dsEntityDataA, dsEntityDataB);
        }

        public string entityNameA = "";
        public string entityNameB = "";
        public string queryServerA = "";
        public string queryServerB = "";

        public bool BuildEntityDataQueries(EntityDataCompreDetails ed)
        {
            string entityIDA = "";
            string entityIDB = "";
            string entityTableNameA = "";
            string entityTableNameB = "";
            string selectAttributesA = "";
            string joinsA = "";
            string hashAttributeA = "";
            string selectAttributesB = "";
            string joinsB = "";
            string hashAttributeB = "";

            if (String.IsNullOrEmpty(ed.EntityMUIDA) || String.IsNullOrEmpty(ed.EntityMUIDB))
            {
                return false;
            }
            
            var dsEntityDetail = GetEntityDetail(ed.EntityMUIDA, ServerA);

            foreach (DataRow dr in dsEntityDetail.Tables[0].Rows)
            {
                entityNameA = DataHelper.dataCellStringConv(dr, "Name");
                entityIDA = DataHelper.dataCellStringConv(dr, "ID");
                entityTableNameA = DataHelper.dataCellStringConv(dr, "EntityTable");
            }

            dsEntityDetail = GetEntityDetail(ed.EntityMUIDB, ServerB);

            foreach (DataRow dr in dsEntityDetail.Tables[0].Rows)
            {
                entityNameB = DataHelper.dataCellStringConv(dr, "Name");
                entityIDB = DataHelper.dataCellStringConv(dr, "ID");
                entityTableNameB = DataHelper.dataCellStringConv(dr, "EntityTable");
            }

            if (String.IsNullOrEmpty(entityIDA) || String.IsNullOrEmpty(entityIDB))
            {
                return false;
            }

            var dsAttributeDetailsA = GetAttributeDetails(entityIDA, ServerA);
            BuildEntityQuery(dsAttributeDetailsA, ServerA, Constants.MDSADMINISTRATION_SERVA);
            selectAttributesA = selectAttributes;
            joinsA = joins;
            hashAttributeA = hashAttribute;

            BuildSelectAttributesCombined(dsAttributeDetailsA);

            var dsAttributeDetailsB = GetAttributeDetails(entityIDB, ServerB);

            if (!checkAttributeDetails(dsAttributeDetailsA, dsAttributeDetailsB))
            {
                return false;
            }

            BuildEntityQuery(dsAttributeDetailsB, ServerB, Constants.MDSADMINISTRATION_SERVB);
            selectAttributesB = selectAttributes;
            joinsB = joins;
            hashAttributeB = hashAttribute;

            queryServerA = BuildEntityDataQuery(hashAttributeA, selectAttributesA, entityTableNameA, joinsA, ServerA,
                Constants.MDSADMINISTRATION_SERVA);
            queryServerB = BuildEntityDataQuery(hashAttributeB, selectAttributesB, entityTableNameB, joinsB, ServerB,
                Constants.MDSADMINISTRATION_SERVB);

            return true;

        }

        

        public string BuildEntityDataQuery(string hashAttribute, string selectAttributes, string entityTableName, string joins, ServerMDSDetails server, string columnPrefix)
        {
            return "SELECT master.dbo.fn_varbintohexstr(HASHBYTES('MD5'," + hashAttribute + ")) AS '" + columnPrefix + Constants.MDSADMINISTRATION_HASHCODE + "'," +
                   selectAttributes + "FROM " + server.Database + ".mdm." + entityTableName + " e" + Environment.NewLine +
                   "INNER JOIN " + server.Database + ".mdm.tblModelVersion mv ON e.[Version_ID] = mv.[ID]" +
                   Environment.NewLine +
                   joins + "WHERE e.[Status_ID] = 1 AND mv.[Name] = '" + server.ModelVersion + "'";
        }

        public static DataSet GetEntityCodeNameData(string entityMUID, ServerMDSDetails server)
        {
            string entityTableName = "";

            var dsEntityDetail = GetEntityDetail(entityMUID, server);

            foreach (DataRow dr in dsEntityDetail.Tables[0].Rows)
            {
                entityTableName = DataHelper.dataCellStringConv(dr, "EntityTable");
            }

            string queryServer = BuildEntityCodeNameDataQuery(entityTableName, server);

            return DataHelper.GetData(queryServer, server);
        }


        public static string BuildEntityCodeNameDataQuery(string entityTableName, ServerMDSDetails server)
        {
            return "SELECT e.Code, e.Name FROM " + server.Database + ".mdm." + entityTableName + " e" + Environment.NewLine +
                   "INNER JOIN " + server.Database + ".mdm.tblModelVersion mv ON e.[Version_ID] = mv.[ID] WHERE e.[Status_ID] = 1 AND mv.[Name] = '" + server.ModelVersion + "'";
        }

        private bool checkAttributeDetails(DataSet dsAttributeDetailsA, DataSet dsAttributeDetailsB)
        {
            if(dsAttributeDetailsA != null && dsAttributeDetailsB != null &&
                dsAttributeDetailsA.Tables.Count > 0 && dsAttributeDetailsB.Tables.Count > 0 &&
                dsAttributeDetailsA.Tables[0].Rows.Count == dsAttributeDetailsB.Tables[0].Rows.Count)
            {
                //check names match
                foreach (DataRow drA in dsAttributeDetailsA.Tables[0].Rows)
                {
                    int result = (from r in dsAttributeDetailsB.Tables[0].AsEnumerable()
                        where r.Field<string>("Name") == drA["Name"].ToString()
                        select r).Count();
                    if (result != 1)
                    {
                        return false;
                    }
                }
                return true;
            }
            return false;
        }

        private DataSet mergeDataSets(DataSet dsEntityDataA, DataSet dsEntityDataB)
        {
            DataSet dsMerge = new DataSet();

            DataTable dt = mergeCreateDataTable(dsEntityDataA, dsEntityDataB);
            
            try
            {
                mergeData(dsEntityDataA, dsEntityDataB, dt);
                dsMerge.Tables.Add(dt);
            }
            catch (SystemException)
            {
                return null;
            }
            return dsMerge;
        }

        private void mergeData(DataSet dsEntityDataA, DataSet dsEntityDataB, DataTable dt)
        {
            dt.Merge(dsEntityDataA.Tables[0]);
            dt.Merge(dsEntityDataB.Tables[0]);
        }

        private DataTable mergeCreateDataTable(DataSet dsEntityDataA, DataSet dsEntityDataB)
        {
            var dt = new DataTable();

            if (dsEntityDataA.Tables[0].Columns.Count != dsEntityDataB.Tables[0].Columns.Count)
            {
                throw new Exception(String.Format("Entity attribute count from Server A does not match Server B ({0})({1})", dsEntityDataA.Tables[0].Columns.Count, dsEntityDataB.Tables[0].Columns.Count));
            }
        
            if (dsEntityDataA.Tables.Count >= 1 && dsEntityDataB.Tables.Count >= 1)
            {   
                dt.Columns.Add(Constants.MDSADMINISTRATION_ISCHECKED, typeof(Boolean));
                
                DataColumn dcCode = new DataColumn(Constants.MDSADMINISTRATION_CODE, typeof(String));
                dt.Columns.Add(dcCode);
                dt.PrimaryKey = new DataColumn[] { dcCode };

                int totalColumns = dsEntityDataA.Tables[0].Columns.Count;

                for (int index = 0; index < totalColumns; index++)
                {
                    DataColumn dcA = dsEntityDataA.Tables[0].Columns[index];
                    DataColumn dcB = dsEntityDataB.Tables[0].Columns[index];

                    if (dcA.ColumnName == Constants.MDSADMINISTRATION_CODE ||
                        dcA.ColumnName == Constants.MDSADMINISTRATION_ISCHECKED)
                    {
                        if(dcA.ColumnName == Constants.MDSADMINISTRATION_CODE)
                        {
                            dsEntityDataA.Tables[0].PrimaryKey = new DataColumn[] { dcA };
                            dsEntityDataB.Tables[0].PrimaryKey = new DataColumn[] { dcB };
                        }

                        continue;
                    }

                    switch (dcA.DataType.FullName)
                    {
                        case Constants.SYSTEM_STRING:
                            {
                                dt.Columns.Add(dcA.ColumnName, typeof(string));
                                dt.Columns.Add(dcB.ColumnName, typeof(string));
                            }
                            break;
                        case Constants.SYSTEM_DATETIME:
                            {
                                dt.Columns.Add(dcA.ColumnName, typeof(DateTime));
                                dt.Columns.Add(dcB.ColumnName, typeof(DateTime));
                            }
                            break;
                        case Constants.SYSTEM_DECIMAL:
                            {
                                dt.Columns.Add(dcA.ColumnName, typeof(Decimal));
                                dt.Columns.Add(dcB.ColumnName, typeof(Decimal));
                            }
                            break;
                        case Constants.SYSTEM_INT32:
                            {
                                dt.Columns.Add(dcA.ColumnName, typeof(Int32));
                                dt.Columns.Add(dcB.ColumnName, typeof(Int32));
                            }
                            break;
                        default:
                            throw new Exception("Unknown DataType for column: " + dcA.DataType.FullName);
                    }
                }
            }

            return dt;
        }

        private void BuildSelectAttributesCombined(DataSet dsAttributeDetails)
        {
            EntityColumns = new Dictionary<string, EntityColumn>();
            foreach (DataRow dr in dsAttributeDetails.Tables[0].Rows)
            {
                bool? isCode = DataHelper.dataCellNullableBoolConv(dr, "IsCode");
                if (isCode.HasValue && isCode == true)
                {
                    EntityColumns.Add(Constants.MDSADMINISTRATION_CODE, new EntityColumn() { Name = Constants.MDSADMINISTRATION_CODE, NameA = Constants.MDSADMINISTRATION_SERVA + Constants.MDSADMINISTRATION_CODE, NameB = Constants.MDSADMINISTRATION_SERVB + Constants.MDSADMINISTRATION_CODE, isDomainEntity = false, isStagingAttribute = false });
                    EntityColumns.Add(Constants.MDSADMINISTRATION_HASH, new EntityColumn() { Name = Constants.MDSADMINISTRATION_HASH, NameA = Constants.MDSADMINISTRATION_SERVA + Constants.MDSADMINISTRATION_HASHCODE, NameB = Constants.MDSADMINISTRATION_SERVB + Constants.MDSADMINISTRATION_HASHCODE, isDomainEntity = false, isStagingAttribute = false });
                }
                else
                {
                    bool isDomainEntity = IsDomainEntity(dr);
                    var name = DataHelper.dataCellStringConv(dr, "Name");
                    EntityColumns.Add(name, new EntityColumn() { Name = name, NameA = Constants.MDSADMINISTRATION_SERVA + name, NameB = Constants.MDSADMINISTRATION_SERVB + name, isDomainEntity = isDomainEntity, isStagingAttribute = false});
                }
            }
        }

        private bool IsDomainEntity(DataRow dr)
        {
            return !String.IsNullOrEmpty(DataHelper.dataCellStringConv(dr, "DomainEntityTableName"));
        }

        public void BuildEntityQuery(DataSet dsAttributeDetails, ServerMDSDetails server, string columnPrefix)
        {
            joins = "";
            selectAttributes = "";
            hashAttribute = "";

            selectAttributes += "CAST(1 AS BIT) AS 'IsChecked'" + Environment.NewLine;
            selectAttributes += ",e.Code" + Environment.NewLine;

            foreach (DataRow dr in dsAttributeDetails.Tables[0].Rows)
            {
                if (!String.IsNullOrEmpty(selectAttributes))
                {
                    selectAttributes += ",";
                }

                selectAttributes += BuildSelectAttribute(dr, columnPrefix) + Environment.NewLine;

                if (IsDomainEntity(dr))
                {

                    joins += "LEFT JOIN " + server.Database + ".mdm." + DataHelper.dataCellStringConv(dr,"DomainEntityTableName") +
                             " " + GetTableAlias(DataHelper.dataCellStringConv(dr,"DomainEntityTableName") + "_" + DataHelper.dataCellStringConv(dr,"Name")) + " ON e.[" + DataHelper.dataCellStringConv(dr,"TableColumn") + "]" +
                             " = " + GetTableAlias(DataHelper.dataCellStringConv(dr, "DomainEntityTableName") + "_" + DataHelper.dataCellStringConv(dr,"Name")) + ".[ID]" + Environment.NewLine;
                }

                if (!String.IsNullOrEmpty(hashAttribute))
                {
                    hashAttribute += " + ";
                }

                hashAttribute += BuildHashAttribute(dr) + Environment.NewLine;


            }
        }

        private string GetTableAlias(string domainEntityKey)
        {
            if (!dTableAlias.ContainsKey(domainEntityKey))
            {
                dTableAlias.Add(domainEntityKey, "t_" + dTableAlias.Count.ToString());
            }
            return dTableAlias[domainEntityKey];
        }
        public DataSet GetAttributeDetails(string entityID, ServerMDSDetails server)
        {
            DataSet dsAttributeDetails = DataHelper.GetData(
                "SELECT a.[Name], a.[TableColumn], a.[AttributeType_ID], a.[DomainEntity_ID], a.[IsCode], a.[IsName]," + Environment.NewLine +
                "CASE WHEN a.[DomainEntity_ID] IS NOT NULL THEN (SELECT e.[EntityTable] FROM mdm.tblEntity e WHERE e.[Id] = a.[DomainEntity_ID])" +
                Environment.NewLine +
                "ELSE NULL END AS 'DomainEntityTableName'" + Environment.NewLine +
                "FROM mdm.tblAttribute a WHERE a.[Entity_ID] = " + entityID +
                " AND (a.[IsSystem] = 0 OR a.[IsCode] = 1 OR a.[IsName] = 1)" + Environment.NewLine +
                " AND MemberType_ID = 1" + Environment.NewLine +
                "ORDER BY a.[IsCode] DESC, a.[IsName] DESC, a.[SortOrder]", server
                );
            return dsAttributeDetails;
        }

        public static DataSet GetEntityDetail(string entityMUID, ServerMDSDetails server)
        {
            DataSet dsEntityDetail = DataHelper.GetData(buildEntityDetailQuery(entityMUID), server);
            return dsEntityDetail;
        }

        public static string buildEntityDetailQuery(string entityMUID)
        {
            return "SELECT e.[ID], e.[EntityTable], e.[Name] FROM mdm.tblEntity e WHERE [e].MUID = '" + entityMUID + "'";
        }

        private string BuildSelectAttribute(DataRow dr, string ColumnPrefix)
        {
            string attributeSelect = "";
            if (IsDomainEntity(dr))
            {
                attributeSelect = "'{' + " + GetTableAlias(DataHelper.dataCellStringConv(dr, "DomainEntityTableName") + "_" + DataHelper.dataCellStringConv(dr, "Name")) + ".Code + '} ' + COALESCE(" +
                                  GetTableAlias(DataHelper.dataCellStringConv(dr, "DomainEntityTableName") + "_" + DataHelper.dataCellStringConv(dr, "Name")) + ".Name, '') AS '" + ColumnPrefix + DataHelper.dataCellStringConv(dr, "Name") + "'" + Environment.NewLine +
                                  "," + GetTableAlias(DataHelper.dataCellStringConv(dr, "DomainEntityTableName") + "_" + DataHelper.dataCellStringConv(dr, "Name")) + ".Code AS '" + ColumnPrefix + DataHelper.dataCellStringConv(dr, "Name") + "_Code'";
                
            }
            else
            {
                attributeSelect = "e." + DataHelper.dataCellStringConv(dr, "TableColumn") + " AS '" + ColumnPrefix + DataHelper.dataCellStringConv(dr, "Name") + "'";

            }

            return attributeSelect;
        }

        private string BuildHashAttribute(DataRow dr)
        {
            string attributeSelect = "";
            if (IsDomainEntity(dr))
            {
                attributeSelect = "COALESCE(CAST(LTRIM(RTRIM(";
                if (!cStates.CompareDataCaseSensitive)
                {
                    attributeSelect += "UPPER(";
                }
                attributeSelect += "'{' + " +
                                   GetTableAlias(DataHelper.dataCellStringConv(dr, "DomainEntityTableName") + "_" +
                                                 DataHelper.dataCellStringConv(dr, "Name")) + ".Code + '} ' + " +
                                   GetTableAlias(DataHelper.dataCellStringConv(dr, "DomainEntityTableName") + "_" +
                                                 DataHelper.dataCellStringConv(dr, "Name")) + ".Name))";
                if (!cStates.CompareDataCaseSensitive)
                {
                    attributeSelect += ")";
                }
                attributeSelect += " AS NVARCHAR(4000)),' ')";
                
            }
            else
            {
                attributeSelect = "COALESCE(CAST(LTRIM(RTRIM(";
                if (!cStates.CompareDataCaseSensitive)
                {
                    attributeSelect += "UPPER(";
                }
                attributeSelect += "e." + DataHelper.dataCellStringConv(dr, "TableColumn") + "))";
                if (!cStates.CompareDataCaseSensitive)
                {
                    attributeSelect += ")";
                }                 
                attributeSelect += " AS NVARCHAR(4000)),' ')";

            }

            return attributeSelect;
        }
    }
}